using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using MySql.Data.MySqlClient;

namespace FutureOpen.Core.func.sql
{
    public class TagProvider
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
       

        public static readonly string INSERT_TAG_MAP = "insert into tagjobmap(tagjobmap_usr, tagjobmap_tag) values({0},{1});update tagjob set tagjob_count=tagjob_count-1 where tagjob_tag={1}";
        public static readonly string INSERT_TAG = "insert into tagjob(tagjob_text, tagjob_count) values('{0}',{1});select last_insert_id();";
        public static readonly string SELECT_TAG = "select * from tagjob where tagjob_text={0}";        
        public static void AddTag(int usrid, string tag)
        {
            tag = tag.ToLower();
            string sql = string.Format(SELECT_TAG, tag);
            object obj = MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null);
            int tid = 0;
            if (obj != null)
            {
                tid = Convert.ToInt32(obj);
            }
            else
            {
                sql = string.Format(INSERT_TAG, tag, 1);
                tid = Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null));
            }
            sql = string.Format(INSERT_TAG_MAP, usrid, tid);
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql, null);        
        }

        public static readonly string SELECT_TAG_BY_USR = "SELECT t.* FROM tagjob t, tagjobmap m where m.tagjobmap_tag=t.tagjob_id and m.tagjobmap_usr={0} order by tagjob_count desc limit {1},{2}";        
        public static DataSet GetTagsByUsr(int uid, int start, int end)
        {
            string sql = string.Format(SELECT_TAG_BY_USR, uid, start, end);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null); 
        }

        public static readonly string SELECT_TAGS = "SELECT * FROM tagjob order by tagjob_count desc limit {0},{1}";
        public static DataSet GetTags(int start, int end)
        {
            string sql = string.Format(SELECT_TAGS, start, end);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }

        

        public static readonly string DELETE_TAG_BY_ID = "delete from tagjobmap where tagjobmap_usr={0} and tagjobmap_tag={1}; update tagjob set tagjob_count=tagjob_count-1 where tagjob_id={0}";
        public static void DeleteTagByUsr(int uid, int tid)
        {
            string sql = string.Format(DELETE_TAG_BY_ID, uid, tid);
            MySqlHelper.ExecuteDatarow(MySqlHelper.CONNSTR(), sql, null);             
        }


        public static DataRow GetTagsByText(string tag)
        {
            string sql = string.Format(SELECT_TAG, tag);
            return MySqlHelper.ExecuteDatarow(MySqlHelper.CONNSTR(), sql, null);     
        }


        public static readonly string SEARCH_ART_BY_TEXT_COUNT = "select count(*) from topic where topic_title like '%{0}%' order by topic_create_time desc";
        public static int GetTopicCountByTag(string tag)
        {
            string sql = string.Format(SEARCH_ART_BY_TEXT, tag);
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null));
        }


        public static readonly string SEARCH_ART_BY_TEXT = "select * from topic where topic_title like '%{0}%' order by topic_create_time desc limit {1},{2}";
        public static DataSet GetTopicByTag(string tag, int p, int psize)
        {
            string sql = string.Format(SEARCH_ART_BY_TEXT, tag, p, p+psize);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }

        public static DataSet GetHotTags(int p, int p_2)
        {
            throw new Exception("The method or operation is not implemented.");
        }
    }
}
